﻿#region

using System;
using System.Data;
using System.Data.SQLite;

#endregion

namespace ALDIOrganizer
{
    /// <summary>
    ///   This static class is responsible for managing SQLite databases
    /// </summary>
    /// <remarks>
    ///   This class is in charge of managing the Employees, Holidays and datatables.
    ///   The User CurrentFile (which contains ) is encrypted and is not an SQLite CurrentFile.
    ///   It is managed by the UserDatabase class.
    /// </remarks>
    public static partial class SQLiteAccess
    {
        private static string _connectionString;
        private static SQLiteConnection _connection;
        private static SQLiteDataAdapter _adapter;
        private static DataSet _dataset;
        private static readonly string CreateDatabaseCommand;

        static SQLiteAccess()
        {
            CreateDatabaseCommand = "CREATE TABLE employees ("
                                    + "id INTEGER PRIMARY KEY NOT NULL, firstName VARCHAR(64), lastName VARCHAR(64), "
                                    + "phone VARCHAR(16), mobile VARCHAR(16), "
                                    + "birthDate DATE, sex BOOLEAN, address VARCHAR(255), nin VARCHAR(16), "
                                    // reduntant columns (calculated in run-Time by the application, not the CurrentFile server)
                                    +
                                    "current_Status STRING, current_employedNow BOOLEAN, current_offProd BOOLEAN, current_contractHrs INTEGER, current_holidayAllowance INTEGER, current_Position INTEGER, "
                                    + "displayColumn VARCHAR(64), onvisa BOOL);"
                                    +
                                    "CREATE TABLE contracts ( id INTEGER PRIMARY KEY NOT NULL, employeeID INTEGER, date DATE, hired BOOLEAN, position INTEGER, contractHRS INTEGER, holidayAllowance INTEGER, comments VARCHAR (255), "
                                    // reduntant columns (calculated in run-Time by the application, not the CurrentFile server)
                                    + "displayColumn VARCHAR(64)); "
                                    +
                                    "CREATE TABLE holidays ( id INTEGER PRIMARY KEY NOT NULL, employeeID INTEGER, startDate DATE, endDate DATE, type INTEGER, pending BOOLEAN, comments VARCHAR (255), "
                                    + "displayColumn VARCHAR(64)"
                                    + "); "
                                    +
                                    "CREATE TABLE requests (id INTEGER PRIMARY KEY NOT NULL, employeeID INTEGER, startDate DATE, endDate DATE, startHour INTEGER, endHOUR INTEGER, weekdays INTEGER, pending BOOLEAN, comments VARCHAR (255));";
        }

        private static bool IsConnectionWorking
        {
            get
            {
                if (_connection == null)
                {
                    return false;
                }
                return _connection.State == ConnectionState.Open;
            }
        }

        internal static void InitializeConnection(string pFullFileName)
        {
            _connectionString = "data source = " + pFullFileName;
            _connection = new SQLiteConnection(_connectionString);
            _connection.Open();
        }

        internal static void CloseConnection()
        {
            if (IsConnectionWorking)
            {
                _connection.Close();
            }
        }

        internal static void CreateNewDatabase(string pFullFileName)
        {
            CloseConnection();
            SQLiteConnection.CreateFile(pFullFileName);
            InitializeConnection(pFullFileName);
            var command = new SQLiteCommand(
                CreateDatabaseCommand,
                _connection);
            command.ExecuteNonQuery();
        }

        /// <summary>
        ///   uploads all the tables to the Dataset (via SQLiteDataAdapter)
        /// </summary>
        internal static void LoadData(string pFullFileName)
        {
            var command = String.Concat(
                "SELECT * FROM employees; ",
                "SELECT * FROM contracts; ",
                "SELECT * FROM holidays; ",
                "SELECT * FROM requests");
            InitializeConnection(pFullFileName);
            _adapter = new SQLiteDataAdapter(command, _connection);
            _dataset = new DataSet();

            _adapter.Fill(_dataset);
            CalculateAllColumns();

            #region setting up GetParent/child relations

            var empIdMasterColumn = _dataset.Tables[0].Columns[0];
            var empIDonContractsColumn = _dataset.Tables[1].Columns[1];

            var drEc = new DataRelation("DRempTocon", empIdMasterColumn, empIDonContractsColumn);
            _dataset.Relations.Add(drEc);

            #endregion

            if (Settings.Default.SweepExpiredRequestsAtStartUp)
            {
                DeleteOutdatedRequests();
            }
            DeleteOutdatedPendingHolidays(); // no setting at the moment

            _employeesForStaffTab = _dataset.Tables[0].DefaultView;
            _employeesForRequestsTab = _dataset.Tables[0].DefaultView;

            SetEmployeesDataView(
                out _employeesForStaffTab,
                String.Empty,
                Settings.Default.SQL_staffmembersDefaultSort);
            SetEmployeesDataView(
                out _employeesForRequestsTab,
                String.Empty,
                Settings.Default.SQL_staffmembersDefaultSort);

            if (Program.frmMain != null)
            {
                Program.frmMain.STAFF_BindControls();
            }
        }
    }
}